package com.hc.daoimp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hc.dao.ContractDao;
import com.hc.entity.Contract;
import com.hc.entity.House;
import com.hc.entity.Tenant;
import com.hc.util.JDBCUtil;

public class ContractDaoImpl implements ContractDao {

	@Override
	public List<Contract> select(String cname, String cdate, String cstate) {
		ArrayList<Contract> arrayList = new ArrayList<>();
		JDBCUtil.sql="SELECT * FROM contract,house,tenant where  contract.hid=house.hid "
				+ "AND contract.tid=tenant.tid AND cname LIKE ? "
				+ "AND cdate LIKE ? AND cstate LIKE ?";
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			JDBCUtil.ps.setString(1, "%"+cname+"%");
			JDBCUtil.ps.setString(2, "%"+cdate+"%");
			JDBCUtil.ps.setString(3, "%"+cstate+"%");
			JDBCUtil.rs=JDBCUtil.ps.executeQuery();
			while(JDBCUtil.rs.next()){
				Contract c=new Contract();
				int id = JDBCUtil.rs.getInt(1);
				String name = JDBCUtil.rs.getString(2);
				String date = JDBCUtil.rs.getString(3);
				String sdate = JDBCUtil.rs.getString(4);
				String edate = JDBCUtil.rs.getString(5);
				int total = JDBCUtil.rs.getInt(6);
				String way = JDBCUtil.rs.getString(7);
				int deposit = JDBCUtil.rs.getInt(8);
				int number = JDBCUtil.rs.getInt(9);
				String qian = JDBCUtil.rs.getString(10);
				String state = JDBCUtil.rs.getString(11);
				int hid = JDBCUtil.rs.getInt(12);
				String xiaoqu = JDBCUtil.rs.getString(16);
				String zuhu = JDBCUtil.rs.getString(32);
				
				c.setCid(id);
				c.setCname(name);
				c.setCdate(date);
				c.setCsdate(sdate);
				c.setCedate(edate);
				c.setCtotal(total);
				c.setCway(way);
				c.setCdeposit(deposit);
				c.setCnumber(number);
				c.setCman(qian);
				c.setCstate(state);
				c.getHouse().setHcommunity(xiaoqu);
				c.getTenant().setTname(zuhu);
				arrayList.add(c);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return arrayList;
	}
	//反显
	@Override
	public Contract toupdate(int id) {
		JDBCUtil.sql="SELECT * FROM contract WHERE cid=?";
		Contract contract=null;
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			JDBCUtil.ps.setInt(1, id);
			JDBCUtil.rs=JDBCUtil.ps.executeQuery();
			while(JDBCUtil.rs.next()){
				
				int cid = JDBCUtil.rs.getInt(1);
				String cname = JDBCUtil.rs.getString(2);
				String cdate = JDBCUtil.rs.getString(3);
				String csdate = JDBCUtil.rs.getString(4);
				String cedate = JDBCUtil.rs.getString(5);
				int ctotal = JDBCUtil.rs.getInt(6);
				String cway = JDBCUtil.rs.getString(7);
				int cdeposit = JDBCUtil.rs.getInt(8);
				int cnumber = JDBCUtil.rs.getInt(9);
				String cman = JDBCUtil.rs.getString(10);
				String cstate = JDBCUtil.rs.getString(11);
				int hid = JDBCUtil.rs.getInt(12);
				int tid = JDBCUtil.rs.getInt(13);
				
				contract=new Contract(cid, cname, cdate, csdate, cedate, ctotal, cway, cdeposit, cnumber, cman, cstate, 
						new House(hid, null, null, 0, 0, 0, 0, null, null, null, 0, null, 0, null, null, null, null), 
						new Tenant(tid, null, 0, null, null, null, null,0));
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return contract;
	}
	@Override
	public List<Tenant> getallteant() {
		ArrayList<Tenant> arrayList = new ArrayList<>();
		JDBCUtil.sql="select * from tenant";
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			JDBCUtil.rs=JDBCUtil.ps.executeQuery();
			while(JDBCUtil.rs.next()){
				Tenant tenant = new Tenant();
				int id = JDBCUtil.rs.getInt(1);
				String name = JDBCUtil.rs.getString(2);
				int phone = JDBCUtil.rs.getInt(3);
				String gender = JDBCUtil.rs.getString(4);
				String address = JDBCUtil.rs.getString(5);
				String number = JDBCUtil.rs.getString(6);
				String date = JDBCUtil.rs.getString(7);
				
				tenant.setTid(id);
				tenant.setTname(name);
				tenant.setTphone(phone);
				tenant.setTgender(gender);
				tenant.setTaddress(address);
				tenant.setTnumber(number);
				tenant.setTaddtime(date);
				
				arrayList.add(tenant);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return arrayList;
	}
	
	@Override
	public List<House> getallhouse() {
		ArrayList<House> arrayList = new ArrayList<>();
		JDBCUtil.sql="select * from house";
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			
			JDBCUtil.rs=JDBCUtil.ps.executeQuery();
			while(JDBCUtil.rs.next()){
				House house = new House();
				house.setHid(JDBCUtil.rs.getInt(1));
				house.setHaddress(JDBCUtil.rs.getString(2));
				house.setHcommunity(JDBCUtil.rs.getString(3));
				house.setHunit(JDBCUtil.rs.getInt(4));
				house.setHfloor(JDBCUtil.rs.getInt(5));
				house.setHnum(JDBCUtil.rs.getInt(6));
				house.setHarea(JDBCUtil.rs.getInt(7));
				house.setHtoward(JDBCUtil.rs.getString(8));
				house.setHdecorate(JDBCUtil.rs.getString(9));
				house.setHshaungqi(JDBCUtil.rs.getString(10));
				house.setHlimit(JDBCUtil.rs.getInt(11));
				house.setHfacilities(JDBCUtil.rs.getString(12));
				house.setHprice(JDBCUtil.rs.getInt(13));
				house.setHstate(JDBCUtil.rs.getString(14));
				house.setHmessage(JDBCUtil.rs.getString(15));
				house.setHaddtime(JDBCUtil.rs.getString(16));
				house.setHupdatetime(JDBCUtil.rs.getString(17));
				arrayList.add(house);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayList;
	}
	//修改
	@Override
	public void update(Contract contract) {
		JDBCUtil.sql="UPDATE contract SET cname=?,cdate=?,csdate=?,cedate=?,ctotal=?,cway=?,"
				+ "cdeposit=?,cnumber=?,cman=?,cstate=? ,hid=?,tid=? WHERE cid=?";
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			JDBCUtil.ps.setString(1, contract.getCname());
			JDBCUtil.ps.setString(2, contract.getCdate());
			JDBCUtil.ps.setString(3, contract.getCsdate());
			JDBCUtil.ps.setString(4, contract.getCedate());
			JDBCUtil.ps.setInt(5, contract.getCtotal());
			JDBCUtil.ps.setString(6, contract.getCway());
			JDBCUtil.ps.setInt(7, contract.getCdeposit());
			JDBCUtil.ps.setInt(8, contract.getCnumber());
			JDBCUtil.ps.setString(9, contract.getCman());
			JDBCUtil.ps.setString(10, contract.getCstate());
			JDBCUtil.ps.setInt(11, contract.getHouse().getHid());
			JDBCUtil.ps.setInt(12, contract.getTenant().getTid());
			JDBCUtil.ps.setInt(13, contract.getCid());
			
			JDBCUtil.ps.executeUpdate();
			System.out.println("dao层更新完成");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	@Override
	public void add(Contract contract) {
		JDBCUtil.sql="INSERT INTO contract VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?)";
		JDBCUtil.ps=JDBCUtil.getPs();
		try {
			JDBCUtil.ps.setString(1, contract.getCname());
			JDBCUtil.ps.setString(2, contract.getCdate());
			JDBCUtil.ps.setString(3, contract.getCsdate());
			JDBCUtil.ps.setString(4, contract.getCedate());
			JDBCUtil.ps.setInt(5, contract.getCtotal());
			JDBCUtil.ps.setString(6, contract.getCway());
			JDBCUtil.ps.setInt(7, contract.getCdeposit());
			JDBCUtil.ps.setInt(8, contract.getCnumber());
			JDBCUtil.ps.setString(9, contract.getCman());
			JDBCUtil.ps.setString(10, contract.getCstate());
			JDBCUtil.ps.setInt(11, contract.getHouse().getHid());
			JDBCUtil.ps.setInt(12, contract.getTenant().getTid());
			
			JDBCUtil.ps.executeUpdate();
			System.out.println("添加成功");
			JDBCUtil.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	//删除
	@Override
	public void del(int id) {
		
		
	}

	
}
